package com.bsg.upm.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelUtil {

	private static Logger logger = Logger.getLogger(ExcelUtil.class);
	private static final String PREFIX_XLS = ".xls";
	private static final String PREFIX_XLSX = ".xlsx";

	public static Map<String, Object> read(File excelFile) {
		Map<String, Object> sheetMap = new HashMap<>();
		Workbook wb = getWorkbook(excelFile);
		int sheetCnt = wb.getNumberOfSheets();
		for (int i = 0; i < sheetCnt; i++) {
			Sheet sheet = wb.getSheetAt(i);
			String sheetName = sheet.getSheetName();
			sheetMap.put(sheetName, readSheet(sheet));
		}
		return sheetMap;
	}

	public static Map<String, Object> read(InputStream is, String fileName) {
		Map<String, Object> sheetMap = new HashMap<>();
		Workbook wb = getWorkbook(is, fileName);
		int sheetCnt = wb.getNumberOfSheets();
		for (int i = 0; i < sheetCnt; i++) {
			Sheet sheet = wb.getSheetAt(i);
			String sheetName = sheet.getSheetName();
			sheetMap.put(sheetName, readSheet(sheet));
		}
		return sheetMap;
	}

	public static Workbook getWorkbook(File excelFile) {
		Workbook wb = null;
		InputStream fis = null;
		try {
			fis = new FileInputStream(excelFile);
			String fileName = excelFile.getName();
			String prefix = fileName.substring(fileName.lastIndexOf("."));
			switch (prefix) {
			case PREFIX_XLS:
				wb = new HSSFWorkbook(fis);
				break;
			case PREFIX_XLSX:
				wb = new XSSFWorkbook(fis);
				break;
			default:
				throw new Exception("文件类型错误");
			}
		} catch (Exception e) {
			logger.error(e.toString());
			e.printStackTrace();
		} finally {
			if (fis != null) {
				try {
					fis.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
		return wb;
	}

	private static List<Object> readSheet(Sheet sheet) {
		int rowCnt = sheet.getLastRowNum() + 1;
		List<Object> dataRowList = new ArrayList<>(rowCnt);
		for (int i = 0; i < rowCnt; i++) {
			Row row = sheet.getRow(i);
			dataRowList.add(readRow(row));
		}
		return dataRowList;
	}

	private static List<Object> readRow(Row row) {
		int cellCnt = row.getLastCellNum();
		List<Object> cellList = new ArrayList<>(cellCnt);
		for (int i = 0; i < cellCnt; i++) {
			Cell cell = row.getCell(i);
			cellList.add(getCellValue(cell));
		}
		return cellList;
	}

	private static Object getCellValue(Cell cell) {
		Object cellValue = null;

		switch (cell.getCellTypeEnum()) {
		case STRING:
			cellValue = StringUtils.trim(cell.getStringCellValue());
			break;
		case NUMERIC:
			if (DateUtil.isCellDateFormatted(cell)) {
				cellValue = cell.getDateCellValue();
			} else {
				cellValue = cell.getNumericCellValue();
			}
			break;
		case BOOLEAN:
			cellValue = cell.getBooleanCellValue();
			break;
		default:
			break;
		}

		return cellValue;
	}

	private static Workbook getWorkbook(InputStream is, String fileName) {
		Workbook wb = null;
		try {
			String prefix = fileName.substring(fileName.lastIndexOf("."));
			switch (prefix) {
			case PREFIX_XLS:
				wb = new HSSFWorkbook(is);
				break;
			case PREFIX_XLSX:
				wb = new XSSFWorkbook(is);
				break;
			default:
				throw new Exception("文件类型错误");
			}
		} catch (Exception e) {
			logger.error(e.toString());
			e.printStackTrace();
		} finally {
			if (wb != null) {
				try {
					wb.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
		return wb;
	}

	public static void mergeCells(Sheet sheet, int startRowNO, int endRowNO, int startCellNO, int endCellNO) {
		CellRangeAddress cra = new CellRangeAddress(startRowNO, endRowNO, startCellNO, endCellNO);
		sheet.addMergedRegion(cra);
	}

	/**
	 * 行复制
	 * 
	 * @param wb
	 * @param fromRow
	 * @param toRow
	 * @param copyValueFlag
	 */
	public static void copyRow(Workbook wb, Row fromRow, Row toRow) {
		copyRow(wb, fromRow, toRow, false, false);
	}

	/**
	 * 行复制
	 * 
	 * @param wb
	 * @param fromRow
	 * @param toRow
	 * @param copyValueFlag
	 */
	public static void copyRow(Workbook wb, Row fromRow, Row toRow, Boolean copyMergeCell, boolean copyValueFlag) {
		toRow.setHeight(fromRow.getHeight());
		for (Iterator<?> cellIt = fromRow.cellIterator(); cellIt.hasNext();) {
			Cell tmpCell = (Cell) cellIt.next();
			Cell newCell = toRow.createCell(tmpCell.getColumnIndex());
			copyCell(wb, tmpCell, newCell, copyValueFlag);
		}
		if (copyMergeCell) {
			Sheet worksheet = fromRow.getSheet();
			for (int i = 0; i < worksheet.getNumMergedRegions(); i++) {
				CellRangeAddress cellRangeAddress = worksheet.getMergedRegion(i);
				if (cellRangeAddress.getFirstRow() == fromRow.getRowNum()) {
					CellRangeAddress newCellRangeAddress = new CellRangeAddress(toRow.getRowNum(),
							(toRow.getRowNum() + (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow())),
							cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn());
					worksheet.addMergedRegionUnsafe(newCellRangeAddress);
				}
			}
		}
	}

	/**
	 * 复制单元格
	 * 
	 * @param wb
	 * @param srcCell
	 * @param distCell
	 * @param copyValueFlag
	 *            true则连同cell的内容一起复制
	 */
	public static void copyCell(Workbook wb, Cell srcCell, Cell distCell, boolean copyValueFlag) {
		// 样式
		distCell.setCellStyle(srcCell.getCellStyle());
		// 评论
		if (srcCell.getCellComment() != null) {
			distCell.setCellComment(srcCell.getCellComment());
		}
		// 不同数据类型处理
		CellType srcCellType = srcCell.getCellTypeEnum();
		distCell.setCellType(srcCellType);
		if (copyValueFlag) {
			switch (srcCellType) {
			case NUMERIC:
				if (DateUtil.isCellDateFormatted(srcCell)) {
					distCell.setCellValue(srcCell.getDateCellValue());
				} else {
					distCell.setCellValue(srcCell.getNumericCellValue());
				}
				break;
			case STRING:
				distCell.setCellValue(srcCell.getRichStringCellValue());
				break;
			case BOOLEAN:
				distCell.setCellValue(srcCell.getBooleanCellValue());
				break;
			case ERROR:
				distCell.setCellErrorValue(srcCell.getErrorCellValue());
				break;
			case FORMULA:
				distCell.setCellFormula(srcCell.getCellFormula());
				break;
			default:
				break;
			}
		}
	}

	/**
	 * 列宽自适应
	 * 
	 * @param sheet
	 * @throws UnsupportedEncodingException
	 */
	public static void autoSizeColumn(Sheet sheet) throws UnsupportedEncodingException {
		// 列宽自适应
		int coloumNum = sheet.getRow(0).getPhysicalNumberOfCells();
		for (int columnIndex = 0; columnIndex < coloumNum; columnIndex++) {
			sheet.autoSizeColumn(columnIndex, true);
		}
		for (int columnIndex = 0; columnIndex < coloumNum; columnIndex++) {
			int columnWidth = sheet.getColumnWidth(columnIndex) / 256;
			for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
				Row row = sheet.getRow(rowNum);
				if (row != null) {
					Cell cell = row.getCell(columnIndex);
					if (cell != null) {
						int length = cell.toString().getBytes("GBK").length;
						if (columnWidth < length + 1) {
							columnWidth = length + 1;
						}
					}
				}
			}
			sheet.setColumnWidth(columnIndex, (columnWidth + 4) * 256);
		}
	}
}
